"""基金业绩表现查询模块"""

PERFORMANCE_SQL = """
SELECT
    sm.ChiName AS 基金全称,
    fa.SecurityCode AS 基金代码,
    TO_CHAR(ROUND(fp.GrowthRateOfNetValue * 100, 2), 'FM999999990.00') || '%' AS 净值增长率,
    TO_CHAR(ROUND(fp.StandardDeviationOfGRONV * 100, 2), 'FM999999990.00') || '%' AS 净值增长率标准差,
    TO_CHAR(ROUND(fp.GrowthRateOfBenchmark * 100, 2), 'FM999999990.00') || '%' AS 业绩比较基准增长率,
    TO_CHAR(ROUND(fp.StandardDeviationOfGROB * 100, 2), 'FM999999990.00') || '%' AS 业绩比较基准标准差,
    TO_CHAR(ROUND((fp.GrowthRateOfNetValue - fp.GrowthRateOfBenchmark) * 100, 2), 'FM999999990.00') || '%' AS 超额收益率,
    TO_CHAR(ROUND((fp.StandardDeviationOfGRONV - fp.StandardDeviationOfGROB) * 100, 2), 'FM999999990.00') || '%' AS 主动风险,
    fp.EndDate AS 报告截至日期,
    fp.StatisticRange AS 净值表现统计区间
FROM (
    SELECT ChiName,
        SecurityCode,
        EndDate
    FROM
    (
        SELECT
            sm2.ChiName,
            fa2.SecurityCode,
            fp2.EndDate
        FROM SecuMain sm2
        INNER JOIN MF_FundArchives fa2 ON sm2.InnerCode = fa2.InnerCode
        INNER JOIN MF_FundPerformance fp2 ON sm2.InnerCode = fp2.InnerCode
        WHERE (:fund_code IS NULL OR fa2.SecurityCode = :fund_code)
        AND (:start_date IS NULL OR fp2.EndDate >= TO_DATE(:start_date, 'YYYY-MM-DD'))  -- 开始日期筛选条件
        AND (:end_date IS NULL OR fp2.EndDate <= TO_DATE(:end_date, 'YYYY-MM-DD'))  -- 结束日期筛选条件
        ORDER BY
            ABS(fp2.EndDate - TO_DATE(:end_date, 'YYYY-MM-DD'))
    )
    WHERE ROWNUM < 2
) t
INNER JOIN SecuMain sm ON t.ChiName = sm.ChiName
INNER JOIN MF_FundArchives fa ON sm.InnerCode = fa.InnerCode AND t.SecurityCode = fa.SecurityCode
INNER JOIN MF_FundPerformance fp ON sm.InnerCode = fp.InnerCode AND t.EndDate = fp.EndDate
WHERE fp.StatisticRange IN (3, 6, 12, 36, 60, 99)
"""

# PERFORMANCE_SQL = """
# SELECT
#     sm.ChiName AS 基金全称,
#     fa.SecurityCode AS 基金代码,
#     TO_CHAR(ROUND(fp.GrowthRateOfNetValue * 100, 2), 'FM999999990.00') || '%' AS 净值增长率,
#     TO_CHAR(ROUND(fp.StandardDeviationOfGRONV * 100, 2), 'FM999999990.00') || '%' AS 净值增长率标准差,
#     TO_CHAR(ROUND(fp.GrowthRateOfBenchmark * 100, 2), 'FM999999990.00') || '%' AS 业绩比较基准增长率,
#     TO_CHAR(ROUND(fp.StandardDeviationOfGROB * 100, 2), 'FM999999990.00') || '%' AS 业绩比较基准标准差,
#     TO_CHAR(ROUND((fp.GrowthRateOfNetValue - fp.GrowthRateOfBenchmark) * 100, 2), 'FM999999990.00') || '%' AS 超额收益率,
#     TO_CHAR(ROUND((fp.StandardDeviationOfGRONV - fp.StandardDeviationOfGROB) * 100, 2), 'FM999999990.00') || '%' AS 主动风险,
#     fp.EndDate AS 报告截至日期,
#     fp.StatisticRange AS 净值表现统计区间
# FROM (
#     SELECT ChiName,
#         SecurityCode,
#         EndDate
#     FROM
#     (
#         SELECT
#             sm2.ChiName,
#             fa2.SecurityCode,
#             fp2.EndDate
#         FROM SecuMain sm2
#         INNER JOIN MF_FundArchives fa2 ON sm2.InnerCode = fa2.InnerCode
#         INNER JOIN MF_FundPerformance fp2 ON sm2.InnerCode = fp2.InnerCode
#         WHERE (:fund_code IS NULL OR fa2.SecurityCode = :fund_code)
#         ORDER BY
#             ABS(fp2.EndDate - TO_DATE(:end_date, 'YYYY-MM-DD'))
#     )
# ) t
# INNER JOIN SecuMain sm ON t.ChiName = sm.ChiName
# INNER JOIN MF_FundArchives fa ON sm.InnerCode = fa.InnerCode AND t.SecurityCode = fa.SecurityCode
# INNER JOIN MF_FundPerformance fp ON sm.InnerCode = fp.InnerCode AND t.EndDate = fp.EndDate
# WHERE fp.StatisticRange IN (3, 6, 12, 36, 60, 99)
# """

# 业绩查询函数
def get_fund_performance(fund_code=None, fund_name=None, start_date=None, end_date=None):
    """获取基金业绩表现"""
    params = {
        'fund_code': fund_code,
        'fund_name': fund_name,
        'start_date': start_date,
        'end_date': end_date
    }
    return PERFORMANCE_SQL, params